import pandas as pd
import os
# 读取指定列的数据
import requests
import pymysql

# 创建会话对象
session = requests.Session()


def login():
    url = "http://127.0.0.1:22080/kcdoc/delegateLogin"
    payload = {'token': '712017165048788d32c953896cc7ce4a'}
    headers = {'Content-Type': 'application/x-www-form-urlencoded'}
    response = session.post(url, data=payload, headers=headers)
    data = response.text
    print(data)


def updateFile(filePath,):
    url = "http://127.0.0.1:22080/kcdoc/uploadFile"
    payload = {'busType': 'processFile','busId':''}
    files = {'file': open(filePath, 'rb')}
    response = session.post(url, data=payload, files=files)
    data = response.text
    print(filePath + "#######" + data)


dataKey = {
    'mmexport1704257252301': 'ae39c2038c654cc4ac6bb1ac84ad6d26'

}
db = pymysql.connect(host='localhost', port=9999, user='root', password='Kc@123456', db="kcdoc")
cursor = db.cursor()
def insertFileSql():
    data = pd.read_excel('C:\\Users\\xyh\\Desktop\\222.xlsx',sheet_name='Sheet1', usecols=['交付流程号', '当前环节', '交货单名称','交货时间'])
    for index, row in data.iterrows():
        newSql = """
           SELECT
                plan.id,
                tr.id 
            FROM
                kc_production_plan plan
                LEFT JOIN kc_process_trajectory tr ON plan.id = tr.production_plan_id 
            WHERE
                plan.production_code = '{0}' 
                AND tr.process_name = '{1}' 
                UNION ALL
            SELECT
                plan.id,
                tr.id 
            FROM
                hi_kc_production_plan plan
                LEFT JOIN hi_kc_process_trajectory tr ON plan.id = tr.production_plan_id 
            WHERE
                plan.production_code = '{2}' 
                AND tr.process_name = '{3}'
        """ .format(row['交付流程号'], row['当前环节'],row['交付流程号'], row['当前环节'])
        cursor.execute(newSql)
        all_error = cursor.fetchall()
        for error in all_error:
            row_ = row['交货单名称']
            inserSql =  """
                INSERT INTO `kc_process_att_relation` ( `trid`, `dirid`, `attid`, `delflag`, `createBy`, `createTime`, `deleteBy`, `deleteTime` )VALUES ( '{0}', 43, '{2}', 0, '00379', '{3}', NULL, NULL );
                """.format(error[1], error[0], dataKey[row_],row['交货时间'])
            print(inserSql)


def pullFile():
    # 指定目录路径
    directory = 'D:\\woker_file\\709\\diff_file\\file'
    login()
    # 遍历目录中的文件
    for filename in os.listdir(directory):
        if os.path.isfile(os.path.join(directory, filename)):
            # 读取文件
            with open(os.path.join(directory, filename), 'r') as file:
                if filename != '.DS_Store':
                    updateFile(directory + '\\' + filename)
if __name__ == '__main__':
       # pullFile()
      insertFileSql()

